package org.stvd.repository.admin.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.stvd.core.dto.QueryResult;
import org.stvd.entities.admin.Roles;
import org.stvd.repository.admin.RolesDao;
import org.stvd.repository.base.impl.BaseDaoImpl;

@Repository("RolesDao")
public class RolesDaoImpl extends BaseDaoImpl<Roles> implements RolesDao {

    @Override
    public List<Roles> findAllRoleByUid(String userId) {
        String hql = "SELECT T1 FROM Roles T1, UserRole T2 WHERE T2.userId = ?0 AND T1.roleId = T2.roleId ";
        return findByHQL(hql, userId);
    }

    @Override
    public QueryResult<Roles> getRoleQueryResult(int start, int limit, String depId, String guid) {
        String hql = "from Roles where guid = ?0 and FIND_IN_SET(depId, ?1) > 0";
        return getQueryResultByHQL(start, limit, hql, guid, depId);
    }

    @Override
    public List<Roles> findRolesByResId(String resId) {
        return findByHQL(
            "select T1 from Roles T1,RoleRes T2 WHERE T1.roleId = T2.roleId AND T1.enable='T' AND T2.resId=?0",
            resId);
    }

    @Override
    public List<Roles> findRolesByUserId(String userId, String guid) {
        return findByHQL(
            "select T1 from Roles T1, UserRole T2 WHERE T1.roleId=T2.roleId AND T1.enable='T' AND T2.userId=?0 AND T2.guid=?1",
            userId, guid);
    }

    @Override
    public List<Roles> findRolesByDepId(String depId, String guid) {
        return findByHQL("from Roles T where T.guid = ?0 and T.depId= ?1 ", guid, depId);
    }

    @Override
    public List<Roles> findValidRolesByDepId(String depId, String guid) {
        return findByHQL("from Roles T where T.guid = ?0 and (T.depId= ?1 or T.buildIn='1')",  guid, depId);
    }
    
    @Override
    public Roles getRolesByRoleCode(String roleCode, String guid) {
        String hql = "FROM Roles WHERE roleCode = ?0 AND guid = ?1 ";
        List<Roles> list = findByHQL(hql, roleCode, guid);
        if(list!=null &&list.size() > 0) {
            return list.get(0);
        }
        return null;
    }
    
    @Override
       public List<Map<String, Object>> listUserNoneRoleGuid(String userId, String roleCode) {
           String sql = " SELECT guid, role_id FROM roles WHERE role_code = :roleCode AND guid NOT IN ("
                   + " SELECT T1.guid from user_role T1, roles T2 WHERE T1.role_id = T2.role_id "
                   + " AND T1.user_id = :userId AND T2.role_code = :roleCode )";
           Map<String, Object> params = new HashMap<>();
           params.put("userId", userId);
           params.put("roleCode", roleCode);
           return findBySQLToMap(0, 0, sql, params);
       }

    @Override
    public List<Map<String, Object>> listUserRoleGuid(String userId, String roleCode) {
        String sql = "SELECT T1.id, T1.role_id, T1.guid FROM user_role T1, roles T2 "
                + " WHERE T1.role_id = T2.role_id "
                   + " AND T1.user_id = :userId AND T2.role_code = :roleCode ";
           Map<String, Object> params = new HashMap<>();
           params.put("userId", userId);
           params.put("roleCode", roleCode);
           return findBySQLToMap(0, 0, sql, params);
    }

    @Override
    @Transactional
    public void deleteUserRoleGuid(String userId, String roleCode) {
        String sql = "DELETE FROM user_role WHERE user_id = :userId AND role_id IN ("
                   + " SELECT role_id FROM roles WHERE role_code = :roleCode)";
        Query query = em.createNativeQuery(sql);
        query.setParameter("userId", userId);
        query.setParameter("roleCode", roleCode);
        query.executeUpdate();
        em.flush();
    }
    
    @Override
    @Transactional
    public void deleteUserRoleGuid(String userId, String roleCode, String guid) {
        String sql = "DELETE FROM user_role WHERE user_id = :userId AND guid = :guid"
                + " AND role_id IN ("
                + " SELECT role_id FROM roles WHERE role_code = :roleCode AND guid = :guid)";
        Query query = em.createNativeQuery(sql);
        query.setParameter("userId", userId);
        query.setParameter("guid", guid);
        query.setParameter("roleCode", roleCode);
        query.executeUpdate();
        em.flush();
    }

    @Override
    public List<Map<String, Object>> listUserRolesByGuid(String guid) {
        String sql = "SELECT r.role_id, r.role_name, r.role_code, ur.user_id FROM "
            + " roles r, user_role ur WHERE r.role_id = ur.role_id AND ur.guid = :guid";
        Map<String, Object> params = new HashMap<>();
        params.put("guid", guid);
        return findBySQLToMap(sql, params);
    }
}
